﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.ApplicationBlocks.Data;
using KP_CommonLibrary.KP_ObjectReferences;
using System.Data;
using System.Data.SqlClient;

namespace KEEPER_BRR_13dotNET.BusinessLogic
{
    public class BL_KPCommon
    {
        public static string EmptySttRec = "0000000000";
        public static string GetNextSttRec()
        {
            object obj = SqlHelper.ExecuteScalar(KP_OCnnSql.Cnn_String, "[dbo].[kp_GetNextSttRec]");
            if (obj != DBNull.Value)
                return obj.ToString();
            else
                throw new Exception("Không tạo được Mã bản ghi mới");
        }
        public static string GetNextSttRec(SqlTransaction transaction)
        {
            object obj = SqlHelper.ExecuteScalar(transaction, "[dbo].[kp_GetNextSttRec]", null);
            if (obj != DBNull.Value)
                return obj.ToString();
            else
                throw new Exception("Không tạo được Mã bản ghi mới");
        }
        public static string GetNextMaHoSoCaNhan()
        {
            object obj = SqlHelper.ExecuteScalar(KP_OCnnSql.Cnn_String, "[dbo].[kp_GetNextMaHoSoCaNhan]");
            if (obj != DBNull.Value)
                return obj.ToString();
            else
                throw new Exception("Không tạo được Mã hồ sơ cá nhân mới ghi mới");
        }
        public static string GetNextHoSoSttRec()
        {
            object obj = SqlHelper.ExecuteScalar(KP_OCnnSql.Cnn_String, "[dbo].[kp_getNextMaHoSo]");
            if (obj != DBNull.Value)
                return obj.ToString();
            else
                throw new Exception("Không tạo được Mã hồ sơ mới ghi mới");
        }
        public static DataTable NextMa_Bn(SqlTransaction transaction,string maLoaiHs,string maHs)
        {
            SqlConnection conn = null;
            bool internalTran = false;
            try
            {
                if (transaction == null)
                {
                    conn = new SqlConnection(KP_OCnnSql.Cnn_String);
                    conn.Open();
                    transaction = conn.BeginTransaction();
                    internalTran = true;
                }
                DataTable value = SqlHelper.ExecuteDataTable(transaction,
                    "[kp_GetNextMaBienNhanHKD]",new object[]{maLoaiHs,maHs});
                if (internalTran)
                    transaction.Commit();
                return value;

            }
            catch (Exception e)
            {
                if (internalTran)
                    transaction.Rollback();
                throw e;
            }
            finally
            {
                if (conn != null && conn.State != ConnectionState.Closed)
                    conn.Close();
            }

        }
        public static DataTable GetDistinctQuocTich()
        {
            string sql = @"Select Distinct Quoc_Tich FROM
                               (Select Distinct Quoc_Tich FROM tblCtThanhVien union all 
                                Select Distinct Quoc_Tich FROM tblHoSoCn) T";
            return SqlHelper.ExecuteDataTable(KP_OCnnSql.Cnn_String, CommandType.Text, sql);
        }

        public static DataTable GetDistinctDanToc()
        {
            string sql = @"Select Distinct Dan_Toc FROM
                               (Select Distinct Dan_Toc FROM tblCtThanhVien union all 
                                Select Distinct Dan_Toc FROM tblHoSoCn) T";
            return SqlHelper.ExecuteDataTable(KP_OCnnSql.Cnn_String, CommandType.Text, sql);
        }

    }
}
